<?php
/*
 *
 *  ADDORDER.PHP
 *
 *
 */

/*
todo:
function DrawTable($select)
{
  if (!$result = MySQL_Query($select, $GLOBALS["spojeni"]))
  {
    while ($pole = MySQL_Fetch_Array())
    {

    }
  }
}
*/

function getRights($user)
{
  $select = "select rights from user where user = '$user'";
  $pole = DbSelect($select);
  if (Is_Array($pole))
  {
    return $pole["rights"];
  }
  else
  {
    return -1;
  }
}

function DTDayOfWeek($engl)
{
  if ($engl == "Mon") return "PO";
  if ($engl == "Tue") return "UT";
  if ($engl == "Wed") return "ST";
  if ($engl == "Thu") return "CT";
  if ($engl == "Fri") return "PA";
  if ($engl == "Sat") return "SO";
  if ($engl == "Sun") return "NE";
}

function DTCompare($date1, $date2)
{
  if (($date1->format("d") == $date2->format("d"))
      && ($date1->format("m") == $date2->format("m"))
      && ($date1->format("Y") == $date2->format("Y")))
  {
    return true;
  }
  else
  {
    return false;
  }
}

function changeStatus($pz, $who, $status)
{
  $id = vrat_max_id("dbupdate") + 1;
  $update = "insert into dbupdate values ('$id', '$who', '$pz', 'status', '$status', '".($status + 1)."', '1', '$who', NOW())";
  DbQuery($update, $who);
  $update = "update podzakazka set status = status + 1 where cislo_objednavky = '$pz'";
  DbQuery($update, $who);
}

function formatNum($num, $prec)
{
  $str = "";
  $format = "%.$prec"."f";
  //echo "$format";
  return sprintf($format, $num);
}

function num2str($num)
{
  $str = str_replace(".", ",", $num);
  return $str;
}

function hasNConfChanges($pz)
{
  $select = "select * from dbupdate where ((relace = '$pz') or (relace = '".getHeadOfPZ($pz)."')) and (confirmed = 0)";
  $pole = DbSelect($select);
  if (Is_Array($pole))
    return true;
  else
    return false;
}

function confirmUpdate($who, $id)
{
  $update = "update dbupdate set confirmed = '1', confirmed_by = '".$_POST["who"]."' where id = $id";
   // ruzna prava? muze zmenu potvrdit obchodnik?
  DbQuery($update, $_POST["who"]);
}

function GetPZChanges($pz)
{
  $table = "podzakazka";
  $select = "select * from dbchanges where query like 'update $table set%' order by tm desc";
  $result = GetDbResult($select);
  if ($result != -1)
  {
    $n = 0;
    while ($zmeny[$n] = GetDbFArray($result))
    {
      $n++;
    }
    for ($i = 0; $i < $n; $i++)
    {
      //echo $zmeny[$i]["query"]." ".$zmeny[$i]["who"]." ".$zmeny[$i]["tm"]." <br>";
      
    }
  }
}

function GetChangeFromQuery($table, $query)
{
  $select = "describe $table";
  $res = GetDbResult($select);
  if ($res != -1)
  {
    if (strstr($query, "insert into $table values"))
    {
        // insert
      $tok = strtok($query, "(,)");
      while ($pole = GetDbFArray($res))
      {
        $value = strtok("(,)");
        $pole_pz[$pole["Field"]] = $value;
      }
      return $pole_pz;
    }
    if (strstr($query, "update $table set"))
    {
        // update
      $query = str_replace("update $table set ", "", $query);
      while (sscanf($query, "%s = %s", $field, $value))
      {
        echo "$field = $value<br>";
        $pole_pz[$field] = $value;
        $query = str_replace("$field = $value", "", $query);
      }
      return $pole_pz;
    }
  }
}

function GetLastChange($table)
{
  $select = "select * from dbchanges where (query like 'insert into $table values%') or (query like 'update $table set%') order by tm desc";
  $result = GetDbResult($select);
  if ($result != -1)
  {
    if (!$posledni = GetDbFArray($result))
    {
      return false;
    }
    echo $posledni["query"]."<br>";
    $pole_update = GetChangeFromQuery($table, $posledni["query"]);
    if (!$predposledni = GetDbFArray($result))
    {
      return false;
    }
    echo $predposledni["query"]."<br>";
    $pole_pz = GetChangeFromQuery($table, $predposledni["query"]);
    if (Is_Array($pole_pz))
    {
      echo $pole_pz["podklady"];
    }
  }
}

function GetDbFArray($result)
{
  return MySQL_Fetch_Array($result);
}

function GetDbResult($query)
{
  if (!$result = MySQL_Query($query, $GLOBALS["spojeni"]))
  {
    $strlog = "".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n";
    logging($strlog);
    if ($debug)
      echo $strlog;
    return -1;
  }
  else
  {
    return $result;
  }
}

function DbSelect($query)
{

  if (!$result = MySQL_Query($query, $GLOBALS["spojeni"]))
  {
    $strlog = "".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n";
    logging($strlog);
    if ($debug)
      echo $strlog;
    return -1;
  }
  else
  {
    if (!$array = MySQL_Fetch_Array($result))
    {
      return 0;
    }
    else
    {
      return $array;
    }
  }
}

function DbQuery($query, $who)
{
  if (!MySQL_Query($query, $GLOBALS["spojeni"]))
  {
    $strlog = "".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n";
    logging($strlog);
    if ($debug)
      echo $strlog;
    return false;
  }
  else
  {
    confirmChanges($who, $query);
    return true;
  }
}

function echoValue($param)
{
  echo "value=\"$param\"";
}

function echoNum($param, $prec)
{
  if ($param != "")
  {
    $str = sprintf("value=\"%.".$prec."f\"", $param);
    echo str_replace(".", ",", $str);
  }
  else
  {
    echo "value=\"\"";
  }
}

function updatePZParam($pz)
{
  $update = "update podzakazka set metru_ctverecnich = ".getPZParam($pz, "m2")." where cislo_objednavky = '$pz'";

  if (!$res = MySQL_Query($update, $GLOBALS["spojeni"]))
  {
    logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
  }
  else
  {
    return $res;
  }
}

function updateZParam($z, $co)
{
  $update = "update zakazka set ";
  if ($co == "naklady")
    $update .= " naklady = ".getZParam($z, $co);

  if ($co == "prijmy")
    $update .= " cena_celkem = ".getZParam($z, $co);

  $update .= " where cislo_objednavky = '$z'";

  if (!$res = MySQL_Query($update, $GLOBALS["spojeni"]))
  {
    logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
  }
  else
  {
    return $res;
  }
}


function getZParam($z, $co)
{
  $select = " select "
            ."  case when sum(m2) < sum(f.sirka * f.vyska * f.pocet_kusu) "
            ."     then "
            ."       sum(f.sirka * f.vyska * f.pocet_kusu) "
            ."     else "
            ."       sum(m2) "
            ."     end as m2, "
            ."     sum(pz.naklad_celkem) as naklady, "
            ."     sum(pz.prijem_celkem) as prijmy "
            ."   from "
            ."     zakazka         as z "
            ."     join podzakazka as pz "
            ."       on (z.id = pz.zakazka_id) "
            ."     join format     as f "
            ."       on (pz.id = f.pz_id) "
            ."   where "
            ."     z.cislo_objednavky = '$z' "
            ."   group by "
            ."     z.id ";

  if (!$res = MySQL_Query($select, $GLOBALS["spojeni"]))
  {
    logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
  }
  else if ($pole = MySQL_Fetch_Array($res))
  {
    return $pole[$co];
  }
  else
  {
    logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
  }
}

function getPZParam($pz, $co)
{
  $select = " select "
            ."  case when sum(m2) < sum(f.sirka * f.vyska * f.pocet_kusu) "
            ."     then "
            ."       sum(f.sirka * f.vyska * f.pocet_kusu) "
            ."     else "
            ."       sum(m2) "
            ."     end as m2, "
            ."     sum(f.naklad) as naklady, "
            ."     sum(f.prijem) as prijmy "
            ."   from "
            ."     podzakazka     as pz "
            ."     join format    as f "
            ."       on (pz.id = f.pz_id) "
            ."   where "
            ."     pz.cislo_objednavky = '$pz' "
            ."   group by "
            ."     pz.id ";

  if (!$res = MySQL_Query($select, $GLOBALS["spojeni"]))
  {
    logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
  }
  else if ($pole = MySQL_Fetch_Array($res))
  {
    return $pole[$co];
  }
  else
  {
    logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
  }
}

function getHeadOfPZ($pz)
{
  sscanf($pz, "%2s-%4s %c", $rok, $c, $chr);

  return "".$rok."-".$c;
}

function getAlphaOfPZ($pz)
{
  sscanf($pz, "%2s-%4s %c", $rok, $c, $chr);

  return $chr;
}

function getLastPZ($z)
{
  $select = "select cislo_objednavky from podzakazka where cislo_objednavky like '$z%' order by cislo_objednavky desc";
  $pole = DbSelect($select);
  if (Is_Array($pole))
  {
    return $pole["cislo_objednavky"];
  }
  else
    return false;
}

function getPrevPZ($pz)
{
  sscanf($pz, "%2s-%4s %c", $rok, $c, $chr);
  if ($chr != 'A')
    return "".$rok."-".$c." ".chr(ord($chr) - 1);
  else
    return "".$rok."-".$c;
}

function getNextPZ($pz)
{
  if (sscanf($pz, "%2s-%4s %c", $rok, $c, $chr) == 3)
    return "".$rok."-".$c." ".chr(ord($chr) + 1);
  else
    return "".$rok."-".$c." A";
}

function vrat_max_id($str)
{
  if (!$GLOBALS["spojeni"])
  {
    logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
    return -1;
  }
  else
  {
    $res = MySQL_Query("select max(id) as id from $str ", $GLOBALS["spojeni"]);
    if (!$res)
    {
       logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
       return -2;
    }
    else
    {
      if (!$cislo = MySQL_Fetch_Array($res))
      {
        logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
        return -3;
      }
      else
      {
        return (int) $cislo["id"];
      }
    }
  }
}

function generuj_cislo_objednavky()
{
  if (!$GLOBALS["spojeni"])
  {
    logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
    return -1;
  }
  else
  {
    $res = MySQL_Query("select cislo_objednavky from zakazka order by cislo_objednavky desc", $GLOBALS["spojeni"]);
    if (!$res)
    {
       logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
       return -2;
    }
    else
    {
      if (!$pole_cislo = MySQL_Fetch_Array($res))
      {
        // neni v DB zadna polozka jeste ... sou
        return "".date("y")."-0001";
        //logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
        //return -3;
      }
      else
      {
        $token = strtok($pole_cislo["cislo_objednavky"], "-");
        $max = (int) strtok("-");
        $cislo = "0000".($max + 1);
        $pole = str_split($cislo);
        return date(y)."-".$pole[strlen($cislo)-4].$pole[strlen($cislo)-3].$pole[strlen($cislo)-2].$pole[strlen($cislo)-1];
      }
    }
  }
}

function logging($str_log)
{
  $id = vrat_max_id("log") + 1;
  $insert = "insert into log values ($id, '".addslashes($str_log)."')";
  MySQL_Query($insert, $GLOBALS["spojeni"]);
}

function confirmChanges($who, $what)
{
  $id = vrat_max_id("dbchanges") + 1;
  $insert = "insert into dbchanges values ($id, '$who', '".addslashes($what)."', CURRENT_TIMESTAMP())";
  if (!MySQL_Query($insert, $GLOBALS["spojeni"]))
  {
    logging("".date("Y/m/d H:i:s").": ".__FILE__.": [".__LINE__."] Error ".MySQL_ErrNo().": ".MySQL_Error($GLOBALS["spojeni"])."\n");
  }
}

function deleteZ($z_id, $who)
{
  $delete = "delete f from format as f join podzakazka as pz on (f.pz_id = pz.id) where pz.zakazka_id = $z_id";
  DbQuery($delete, $who);
  $delete = "delete from podzakazka where zakazka_id = $z_id";
  DbQuery($delete, $who);
  $delete = "delete from zakazka where id = $z_id";
  DbQuery($delete, $who);
  $delete = "delete from vztah_zakazka_typ where zakazka_id = $z_id";
  DbQuery($delete, $who);
  $delete = "delete from dbupdate where relace like (select concat(cislo_objednavky, '%') from zakazka where id = $z_id)";
  DbQuery($delete, $who);
}

function deletePZ($pz_id, $who)
{
  $delete = "delete f from format as f join podzakazka as pz on (f.pz_id = pz.id) where pz.id = $pz_id";
  DbQuery($delete, $who);
  $delete = "delete from podzakazka where id = $pz_id";
  DbQuery($delete, $who);
  $delete = "delete from dbupdate where relace = '$pz_id'";
  DbQuery($delete, $who);
}

function deleteK($k_id, $who)
{
  $delete = "delete a from adresa as a join klient as k on (k.adresa_id = a.id) where k.id = $k_id";
  DbQuery($delete, $who);
  $delete = "delete a from adresa as a join klient as k on (k.korespondencni_adresa_id = a.id) where k.id = $k_id";
  DbQuery($delete, $who);
  $delete = " delete a, a2, a3, a4, a5"
            ." from "
            ."   kontaktni_osoba as ko "
            ."   join klient as k on (ko.klient_id = k.id) "
            ."   left join adresa as a on (k.adresa_id = a.id) "
            ."   left join adresa as a2 on (k.korespondencni_adresa_id = a2.id) "
            ."   left join adresa as a3 on (ko.adresa_fakturacni_id = a3.id) "
            ."   left join adresa as a4 on (ko.adresa_korespondencni_id = a4.id) "
            ."   left join adresa as a5 on (ko.adresa_id = a5.id) "
            ." where k.id = $k_id ";
  //echo $delete."<br>";
  DbQuery($delete, $who);
  $delete = "delete ko from kontaktni_osoba as ko join klient as k on (ko.klient_id = k.id) where k.id = $k_id";
  DbQuery($delete, $who);
  $delete = "delete from klient where id = $k_id";
  DbQuery($delete, $who);
}

?>

